package xmu.zfw.util;


import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import xmu.zfw.vo.ReportVO;

import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

public class ExcelImporter {

    public static String getCellValue(Cell cell){
        if(cell == null){
            return null;
        }
        if(cell.getCellTypeEnum().equals(CellType.STRING)){
            return cell.getStringCellValue().trim();
        }
        if(cell.getCellTypeEnum().equals(CellType.BLANK)){
            return null;
        }
        if(cell.getCellTypeEnum().equals(CellType.NUMERIC)){
            return String.valueOf(cell.getNumericCellValue());
        }
        return null;
    }

    public static String getCellDate(Cell cell) throws IllegalArgumentException{
        if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){
            return null;
        }
        if(cell.getCellType() != Cell.CELL_TYPE_NUMERIC){
            throw new IllegalArgumentException("日期类型不对");
        }
        try {
            java.util.Date dateUtil = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());

            return (new Date(dateUtil.getTime())).toString();
        }catch (Exception e){
            throw new IllegalArgumentException("日期类型不对");
        }
    }

    public static Boolean check(ReportVO reportVO){
        if(reportVO.getName().equals("")){
            return false;
        }
        if(reportVO.getQuestion().equals("")){
            return false;
        }
        if(reportVO.getNo().equals("")){
            return false;
        }
        return true;
    }

    public static List<ReportVO> importExcel(Workbook wb){
        List<ReportVO> reportVOList = new ArrayList<>();
        Sheet sheet = wb.getSheetAt(0);
        int lastRow = sheet.getLastRowNum() + 1;
        int numCells = sheet.getRow(0).getPhysicalNumberOfCells();
        final int cols = 24;
        if(numCells != cols || lastRow <= 1){
            return null;
        }

        for(int i = 1; i < lastRow; i++){
            Row row = sheet.getRow(i);
            ReportVO reportVO = new ReportVO();
            reportVO.setNo(getCellValue(row.getCell(0)));
            reportVO.setName(getCellValue(row.getCell(1)));
            reportVO.setCardId(getCellValue(row.getCell(2)));
            reportVO.setPhone(getCellValue(row.getCell(3)));
            reportVO.setLocation(getCellValue(row.getCell(4)));

            if(row.getCell(5) == null || row.getCell(6) == null){
                return null;
            }
            String[] reportederNames = row.getCell(5).getStringCellValue().split("\\s+");
            String[] reportederDepartments = row.getCell(6).getStringCellValue().split("\\s+");
            if(reportederNames.length != reportederDepartments.length){
                return null;
            }
            JSONArray reporteders = new JSONArray();
            for (int j = 0; j < reportederNames.length; j++){
                if(reportederNames[j].equals("") || reportederDepartments[j].equals("")){
                    continue;
                }
                JSONObject reporteder = new JSONObject();
                reporteder.put("blackName", reportederNames[j]);
                reporteder.put("blackDepartment", reportederDepartments[j]);
                reporteders.add(reporteder);
            }
            reportVO.setBlackInfo(reporteders);

            reportVO.setQuestion(getCellValue(row.getCell(7)));
            reportVO.setIsSend(getCellValue(row.getCell(8)));
            reportVO.setMoreLetter(getCellValue(row.getCell(9)));
            reportVO.setInvolvedUnit(getCellValue(row.getCell(10)));
            reportVO.setInvolvedRegion(getCellValue(row.getCell(11)));
            reportVO.setInvolvedArea(getCellValue(row.getCell(12)));
            reportVO.setEnterer(getCellValue(row.getCell(13)));
            reportVO.setReportType(getCellValue(row.getCell(14)));
            reportVO.setMailNo(getCellValue(row.getCell(15)));
            try {
                reportVO.setSendTime(getCellDate(row.getCell(16)));
            }catch (IllegalArgumentException e){
                return null;
            }


            reportVO.setSendDepartment(getCellValue(row.getCell(17)));
            if(getCellValue(row.getCell(18)) != null){
                String tmp = getCellValue(row.getCell(18));
                reportVO.setFeedbackTimeLimit(tmp.split("\\.")[0]);
            }

            try {
                reportVO.setFeedbackTime(getCellDate(row.getCell(19)));
            }catch (IllegalArgumentException e){
                return null;
            }

            reportVO.setConclusion(getCellValue(row.getCell(20)));
            reportVO.setProgress(getCellValue(row.getCell(21)));
            reportVO.setLevel(getCellValue(row.getCell(22)));
            reportVO.setIsRepeat(getCellValue(row.getCell(23)));

            if(!check(reportVO)){
                return null;
            }
            reportVOList.add(reportVO);
        }
        return reportVOList;
    }
}
